import pandas as pd
from sqlalchemy import create_engine, text

from data_base_tool import SQLDatabaseTool
from tool_utils import get_sql_lite_tool
from tool_utils import get_sql_databaseTool


def data_sources_management_page(st):
    st.title("数据源管理")

    sqlite_tool = get_sql_lite_tool()

    # 显示现有数据源
    st.subheader("已配置的数据源")
    data_sources = sqlite_tool.get_table_data('database_connections',where_clause=None,limit=-1).get('data')

    if not data_sources:
        st.info("暂无数据源，请点击下方按钮添加")
    else:
        set_table(st,data_sources)

    show_table_data(st)
    # ------------------------- 添加/编辑数据源表单 -------------------------
    edit_add_from(sqlite_tool, st)

def set_table(st, data_sources):
    sqlite_tool = get_sql_lite_tool()

    # Pagination settings
    items_per_page = 5
    if 'page' not in st.session_state:
        st.session_state.page = 1
    
    # Calculate total pages
    total_items = len(data_sources)
    total_pages = (total_items + items_per_page - 1) // items_per_page
    
    # Get current page data
    start_idx = (st.session_state.page - 1) * items_per_page
    end_idx = min(start_idx + items_per_page, total_items)
    page_data = data_sources[start_idx:end_idx]
    
    # 表头
    cols = st.columns([2, 2, 2, 1, 1])
    cols[0].markdown("**名称**")
    cols[1].markdown("**类型**")
    cols[2].markdown("**连接信息**")
    cols[3].markdown("**操作**")
    cols[4].markdown("**操作**")
    
    # Display current page data
    for idx, source in enumerate(page_data):
        cols = st.columns([2, 2, 2, 1, 1])

        # 名称可点击（按钮形式）
        if cols[0].button(
                f"📁 {source['name']}",
                key=f"name_{idx + start_idx}",
                use_container_width=True
        ):
            st.session_state.selected_source = source
            st.session_state.show_tables = True  # 触发显示表列表

        cols[1].write(source["type"])
        schema_label = ''
        schema_name = source.get('schema_name')
        if schema_name is not None:
            schema_label = f"Schema: {source.get('schema_name', 'N/A')}"
        cols[2].code(
            f"Host: {source.get('host', 'N/A')}\nPort: {source.get('port', 'N/A')}\nDB: {source.get('database', 'N/A')}\n{schema_label}")

        # 编辑按钮
        if cols[3].button("✏️ 编辑", key=f"edit_{idx + start_idx}"):
            st.session_state.edit_source = source

        # 删除按钮
        if cols[4].button("🗑️ 删除", key=f"del_{idx + start_idx}"):
            sqlite_tool.delete_data('database_connections', where_clause=f"id={source['id']}")
            st.rerun()
    
    # Pagination controls
    col1, col2, col3 = st.columns([1, 2, 1])
    with col1:
        if st.button('上一页', disabled=st.session_state.page == 1):
            st.session_state.page -= 1
            st.rerun()
    
    with col2:
        st.write(f"第 {st.session_state.page} 页 / 共 {total_pages} 页")
    
    with col3:
        if st.button('下一页', disabled=st.session_state.page == total_pages):
            st.session_state.page += 1
            st.rerun()

def edit_add_from(sqlite_tool, st):
    with st.form("data_source_form"):
        st.subheader("添加新数据源" if not st.session_state.edit_source else "编辑数据源")

        # 表单字段
        source_name = st.text_input("数据源名称",
                                    value=st.session_state.edit_source.get("name",
                                                                           "") if st.session_state.edit_source else "")
        source_type = st.selectbox(
            "类型",
            ["MySQL", "PostgreSQL",'SQLSERVER'],
            index=0 if not st.session_state.edit_source else ["MySQL", "PostgreSQL",'SQLSERVER'].index(
                st.session_state.edit_source["type"])
        )

        print(source_type)

        # 数据库配置字段
        if source_type in ["MySQL", "PostgreSQL",'SQLSERVER']:
            col1, col2 = st.columns(2)
            host = col1.text_input("主机地址",
                                   value=st.session_state.edit_source.get("host",
                                                                          "") if st.session_state.edit_source else "")
            port = col2.number_input("端口",
                                     value=st.session_state.edit_source.get("port",
                                                                            3306) if st.session_state.edit_source else 3306)
            database_col, shcema_name_col = st.columns(2)
            database = database_col.text_input("database",
                                     value=st.session_state.edit_source.get("database",
                                                                            "") if st.session_state.edit_source else "")

            schema_name = shcema_name_col.text_input("schema_name(select)",
                                         value=st.session_state.edit_source.get("schema_name",
                                                                                "") if st.session_state.edit_source else "")

            username = st.text_input("用户名",
                                     value=st.session_state.edit_source.get("username",
                                                                            "") if st.session_state.edit_source else "")
            password = st.text_input("密码", type="password",
                                     value=st.session_state.edit_source.get("password",
                                                                            "") if st.session_state.edit_source else "")

        # 文件配置字段
        if source_type in ["CSV", "Excel"]:
            file_path = st.text_input("文件路径",
                                      value=st.session_state.edit_source.get("file_path",
                                                                             "") if st.session_state.edit_source else "")

        # 表单提交按钮
        submit_cols = st.columns([4, 1])
        if submit_cols[0].form_submit_button("💾 保存配置"):
            new_source = {
                "name": source_name,
                "type": source_type
            }

            if source_type in ["MySQL", "PostgreSQL",'SQLSERVER']:
                new_source.update({
                    "host": host,
                    "port": port,
                    "database": database,
                    "username": username,
                    "password": password,
                    "schema_name": schema_name
                })
            else:
                new_source["file_path"] = file_path

            # 更新或新增数据源
            if st.session_state.edit_source:
                print('update source{}', new_source)
                sqlite_tool.update_data('database_connections', new_source,
                                        where_clause=f"id={st.session_state.edit_source['id']}", params=new_source)
            else:
                # 添加新数据源
                print('add new source{}', new_source)
                sqlite_tool.insert_data('database_connections', new_source)

            st.session_state.edit_source = None
            st.rerun()

        # 取消编辑按钮
        if st.session_state.edit_source and submit_cols[1].form_submit_button("❌ 取消"):
            st.session_state.edit_source = None
            st.rerun()


def show_table_data(st):
    if st.session_state.get('selected_source'):
        # 显示/隐藏按钮
        if st.button("🔄 切换表数据显示", key="toggle_tables"):
            if st.session_state.get('show_tables'):
                st.session_state.show_tables = False
            else:
                st.session_state.show_tables = True

        if st.session_state.get('show_tables'):
            st.subheader("表数据")
            try:
                if st.session_state.selected_source['type'] in ['MySQL', 'PostgreSQL','SQLSERVER']:
                    # 创建数据库连接
                    sql_tool = get_sql_databaseTool(st.session_state.selected_source['id'])
                    engine = sql_tool.engine

                    # 获取所有表名
                    with engine.connect() as connection:
                        tables = sql_tool.get_table_names()


                    # 显示表选择器
                    selected_table = st.selectbox("选择表", tables)

                    # 显示表数据
                    if selected_table:
                        sql_tool = get_sql_databaseTool(st.session_state.selected_source['id'])
                        try:
                            df = sql_tool.get_table_data_frame(selected_table, size = 100)
                            st.dataframe(df)
                        except Exception as e:
                            st.error(f"无法读取表数据: {str(e)}")

            except Exception as e:
                st.error(f"连接数据库失败: {str(e)}")